<?php

class group {

	public static function getUserGroups($uID, $limit = 5){
		if(is_int($uID)){
			$dbCon = db::singleton();
			$sql = "SELECT group_gID, isAdmin, active, group.name, group.description FROM " . SYS_DB_DBNAME . "." . SYS_DB_PREFIX .  "group_user LEFT JOIN " . SYS_DB_DBNAME . "." . SYS_DB_PREFIX . "group ON " . SYS_DB_PREFIX . "group_user." . SYS_DB_PREFIX . "group_gID=" . SYS_DB_PREFIX . "group.gID WHERE user_uID = " . $uID . " LIMIT 0, " . $dbCon->real_escape_string($limit);
			$result = $dbCon->single($sql);
			return $result;
		} else {
			throw new PicBoardException("Fetching user groups failed; uID must be an integer.");
		}
	}

	public static function getGroupList($ID, $start, $end){
		// Get charts for either uID or gID. Also fetch the total amount of records.

		$dbCon = db::singleton();

		$sql = "SELECT SQL_CALC_FOUND_ROWS group_gID, isAdmin, active, group.name, group.description FROM " . SYS_DB_DBNAME . "." . SYS_DB_PREFIX .  "group_user LEFT JOIN " . SYS_DB_DBNAME . "." . SYS_DB_PREFIX . "group ON " . SYS_DB_PREFIX . "group_user." . SYS_DB_PREFIX . "group_gID=" . SYS_DB_PREFIX . "group.gID WHERE user_uID = " . $ID . "  AND active=1 LIMIT " . $dbCon->real_escape_string((int)$start) . "," . $dbCon->real_escape_string((int)$end);

		$result = $dbCon->single( $sql );

		$numRows = $dbCon->oneRow( "SELECT FOUND_ROWS()" );
		
		return array_merge($result, $numRows);				
		
	}	
	
	public static function getAssociatedAssets(user &$userObj, $gID){
		$assets = array();
		
		// Get charts
		$assets['charts'] = chart::getGroupCharts($gID);
		
		// Get books
		$assets['books'] = book::getGroupBooks($gID);
		
		// Get albums
		$assets['albums'] = images::getGroupLists($gID);
		
		if(count($assets['albums']) > 0){
			// Get photos
			$assets['photos'] = images::getListImages($albums['isID']);
		} else {
			$assets['photos'] = array();
		}
		
		return $assets;
	}
	
	public static function getGroupInfo($gID){
		if(is_integer($gID)){
			/*SELECT group.gID, group.name, group.description, group.private, group.byInvitation, institution.name AS instName
			FROM picboard.group
			LEFT JOIN picboard.institution
			ON institution.instID=group.institution_instID
			WHERE gID=1*/
			$sql = "SELECT group.gID, group.name, group.description, group.private, group.byInvitation, institution.name AS instName, institution.instID FROM " . SYS_DB_DBNAME . "." . SYS_DB_PREFIX . "group LEFT JOIN " . SYS_DB_DBNAME . ".institution ON institution.instID=group.institution_instID WHERE gID=" . $gID;
			$dbCon = db::singleton();
			
			$result = $dbCon->oneRow($sql);
			return $result;
		} else {
			throw new PicBoardException("Fetching group information failed; gID must be an integer.");
		}
	}
	
	public static function getPendingRequests($gID){
		if(is_integer($gID)){
			/*
			SELECT user_uID, user.userName, user.firstName, user.lastName
			FROM picboard.group_user
			LEFT JOIN picboard.user ON user.uID=group_user.user_uID
			LEFT JOIN picboard.group ON group_user.group_gID=group.gID
			WHERE group_user.active=0 AND group_gID=1 AND 	group.byInvitation=0	
			*/
			$dbCon = db::singleton();
			
			$sql = "SELECT user_uID, user.userName, user.firstName, user.lastName FROM " . SYS_DB_DBNAME . ".group_user LEFT JOIN " . SYS_DB_DBNAME . ".user ON user.uID=group_user.user_uID LEFT JOIN " . SYS_DB_DBNAME . ".group ON group_user.group_gID=group.gID WHERE group_user.active=0 AND group.byInvitation=0 AND group_gID=" . $dbCon->real_escape_string($gID);
			$result = $dbCon->single($sql);
			return $result;
		} else {
			throw new PicBoardException("Fetching group information failed; parameter 1 (gID) must be an integer.");
		}
	}
	
	public static function getUserList(user &$userObj, $gID){
		/*
		SELECT SQL_CALC_FOUND_ROWS uID, isAdmin, group_user.active, firstName, lastName
		FROM picboard.group_user
		LEFT JOIN picboard.user ON user.uID=group_user.user_uID
		WHERE group_gID=1		
		*/
		
		if( $userObj->isInGroup($gID) ){
			$dbCon = db::singleton();
			
			$sql = "SELECT SQL_CALC_FOUND_ROWS uID, isAdmin, group_user.active, firstName, lastName FROM " . SYS_DB_DBNAME . ".group_user LEFT JOIN " . SYS_DB_DBNAME . ".user ON user.uID=group_user.user_uID WHERE group_gID=" . $dbCon->real_escape_string($gID);
			
			$result = $dbCon->single($sql);
			
			return $result;
		}
	}
	
	public static function getGroupImportedImageSet(user &$userObj, $gID){
		// We need to create the imported imageset if its not already there. Check for existance.
		
		if( $userObj->isInGroup($gID) ){
			$dbCon = db::singleton();
			
			$sql = "SELECT isID FROM " . SYS_DB_DBNAME . ".imageset WHERE group_gID = " . $dbCon->real_escape_string($gID) . " AND name = \"Imported Photos\"";
			
			$result = $dbCon->oneRow($sql);
			
			if($result['isID'] == "null" || $result['isID'] == ""){
				// imageset doesn't exist yet.
				$sql = "INSERT INTO " . SYS_DB_DBNAME . ".imageset (group_gID, name, private) VALUES(" . $dbCon->real_escape_string($gID) . ", 'Imported Photos', 1)";
				$dbCon->single($sql);
				
				$result['isID'] = $dbCon->insert_id;
			}
		}
		
		return (int)$result['isID'];
	}
	
	public static function canLeaveGroup(user &$userObj, $gID){
		// Check to see if the user is allowed to leave (if they are the only admin for eg, they cannot)
		$dbCon = db::singleton();
		$gID = $dbCon->real_escape_string($gID);
		$sql = "SELECT COUNT(*) AS Admins, (SELECT COUNT(*) FROM " . SYS_DB_DBNAME . ".group_user WHERE group_gID=$gID AND user_uID=" . $userObj->getuID() . " AND isAdmin=1 AND active=1) AS isCurrentUser FROM " . SYS_DB_DBNAME . ".group_user WHERE group_gID=$gID AND isAdmin=1 AND active=1 GROUP BY group_gID";
		$result = $dbCon->oneRow($sql);
		
		if($result['Admins'] == 1 && $result['isCurrentUser'] == 1) return false;
		
		return true;
	}
	
	public static function leaveGroup(user &$userObj, $gID){
		$dbCon = db::singleton();
		if( isset($gID) ){
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".group_user WHERE user_uID=" . $dbCon->real_escape_string((int)$userObj->getuID()) . " AND group_gID=" . $dbCon->real_escape_string((int)$gID);
			$dbCon->single($sql);
			return true;
		} else {
			return false;
		}
	}
	
	public static function joinGroup(user &$userObj, $gID, $type){
		$uID = $userObj->getuID();
		return group::joinGroupWithUserId($userObj, $uID, $gID, $type);
	}
	
	public static function joinGroupWithUserId(user &$userObj, $userID, $gID, $type, $isAdmin = false){
		$dbCon = db::singleton();
		$uID = $userID;
		
		if(group::userIdInGroup($userID, $gID)) {
			return array("status" => "false", "reason" => "User already in group");
		}
		
		$sql = "INSERT INTO " . SYS_DB_DBNAME . ".group_user (user_uID, group_gID, isAdmin, active, invitation) ";
		switch($type){
		
			case "JoinRequest":
				// The user is making a request to join the group.
				$sql .= "VALUES (" . $dbCon->real_escape_string($uID) . "," . $dbCon->real_escape_string($gID) . ",0,0, 0)";
				
				// Send an email / message to group admin?
			break;
			
			case "Open":
				// The group is open, just add then to the group.
				$sql .= "VALUES (" . $dbCon->real_escape_string($uID) . "," . $dbCon->real_escape_string($gID) . ",0,1, 0)";
			break;
			
			case "Invite":
				$admin = (int)$isAdmin;
				$sql .= "VALUES (" . $dbCon->real_escape_string($uID) . "," . $dbCon->real_escape_string($gID) . ",". $admin . ", 0, 1)";
				
				// Send the user a message stating that they have been accepted into this group
				
				$groupInfo = group::getGroupInfo((int)$gID);
				
				$messaging = new messaging($userObj);
				
				$messaging->sendMessage(
					(int)$uID, 
					"Group Invitation", 
					"You have been invited to join the group '" . $groupInfo['name'] . "' by " . $userObj->getUserName() . ".", 
					(int)$gID, 
					messaging::FROM_GROUP
				);
				
			break;
		
		}
		
		$dbCon->single( $sql );
		return array("status" => true, "action" => $type);	
	}
	
	public static function acceptJoinRequest(user &$userObj, $gID, $uID){
		if(is_integer($gID) == false || is_integer($uID) == false) throw new PicBoardException('uID / gID are not integers.');
		// Does the user have permissions for this group?
		if($userObj->isInGroup($gID, user::GROUP_ADMINONLY)){
			$dbCon = db::singleton();
			$sql = "UPDATE " . SYS_DB_DBNAME . ".group_user SET active=1 WHERE group_gID=" . $dbCon->real_escape_string($gID) . " AND user_uID=" . $dbCon->real_escape_string($uID);
			$dbCon->single($sql);
			
			// Send the user a message stating that they have been accepted into this group
			
			$groupInfo = group::getGroupInfo((int)$gID);
			
			$messaging = new messaging($userObj);
			
			$messaging->sendMessage(
				$uID, 
				"GroupAcceptance", 
				"The admin at " . $groupInfo['name'] . " has accepted your application to join the group.", 
				$gID, 
				messaging::FROM_GROUP
			);
			
			
			return true;
		}
	}
	
	public static function rejectJoinRequest(user &$userObj, $gID, $uID){
		if(is_integer($gID) == false || is_integer($uID) == false) throw new PicBoardException('uID / gID are not integers.');

		if($userObj->isInGroup($gID, user::GROUP_ADMINONLY)){
			$dbCon = db::singleton();
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".group_user WHERE group_gID=" . $dbCon->real_escape_string($gID) . " AND user_uID=" . $dbCon->real_escape_string($uID);
			$dbCon->single($sql);
			return true;		
		}	
	}
	
	public static function canJoinGroup(user &$userObj, $gID){
		$dbCon = db::singleton();
		/*SELECT active, group.private, group.byInvitation
		FROM group_user
		LEFT JOIN picboard.group ON group.gID=group_user.group_gID
		WHERE group_gID=1 AND user_uID=1*/
		$sql = "SELECT group.private, group.byInvitation FROM " . SYS_DB_DBNAME .".group WHERE group.gID=" . $dbCon->real_escape_string($gID);
		$result = $dbCon->oneRow($sql);
		
		// Is this an invite-only group?
		if($result['private'] == 1 && $result['byInvitation'] == 1){
			return array("status" => false, "message" => "InviteOnly");
		}
		
		if($result['private'] == 0){
			return array("status" => true, "message" => "Open");
		}
		
		if($result['private'] == 1 && $result['byInvitation'] == 0){
			return array("status" => true, "message" => "JoinRequest");
		}
		
		// Check to see if the group is open. If not, do they have an invite?
		// If its application only, return true, but the user will have to wait for an admin to validate.
	}
	
	public static function userIdInGroup($userID, $gID){
		$dbCon = db::singleton();
		
		$sql = "SELECT COUNT(*) AS userExists FROM " . SYS_DB_DBNAME . ".group_user WHERE user_uID=" . (int)$dbCon->real_escape_string($userID) . " AND group_gID=" . (int)$dbCon->real_escape_string($gID);
		$res = $dbCon->oneRow($sql);
		
		return (BOOL)$res['userExists'];
	}
	
	public static function acceptInvitation(user &$userObj, $groupID, $msgID = null){
		$dbCon = db::singleton();
		
		$sql = "UPDATE " . SYS_DB_DBNAME . ".group_user SET active=1, invitation=0 WHERE user_uID=" . (int)$userObj->getuID() . " AND group_gID=" . (int)$groupID . ";";
		
		$dbCon->single($sql);
		
		if($msgID != null){
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".message WHERE to_uID=" . (int)$userObj->getuID() . " AND msgID=" . (int)$dbCon->real_escape_string($msgID);
			$dbCon->single($sql);
		}
		
		return true;
	}
	
	public static function rejectInvitation(user &$userObj, $groupID, $msgID = null){
		$dbCon = db::singleton();
		
		$sql = "DELETE FROM " . SYS_DB_DBNAME . ".group_user WHERE user_uID=" . (int)$userObj->getuID() . " AND group_gID=" . (int)$groupID . ";";
		
		$dbCon->single($sql);
		
		if($msgID != null){
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".message WHERE to_uID=" . (int)$userObj->getuID() . " AND msgID=" . (int)$dbCon->real_escape_string($msgID);
			$dbCon->single($sql);
		}
		
		return true;
	}
	
	// Return true or false whether this user can create this group or not.
	public static function canCreateGroup(user &$userObj, array $newGroup){
		// Is the group name unique in this context (either global groups, or instutution?)
		$dbCon = db::singleton();
		$sql = "SELECT COUNT(*) AS conflicts FROM " . SYS_DB_DBNAME . ".group WHERE LOWER(name) = LOWER('" . $dbCon->real_escape_string($newGroup['Name']) . "') AND institution_instID='" . $dbCon->real_escape_string($newGroup['Institution']) . "'";
		// Additional tests...
		$result = $dbCon->oneRow($sql);
		
		if($newGroup['Institution'] != 1){
			// The user has selected a different institution. Are they allowed to create a group for this institution?
			$institutions = $userObj->rtnInstitution();
			//print_r($institutions);
			
			$hasAccess = false;
			foreach($institutions as $inst){
				if($inst['instID'] == $newGroup['Institution']) return true;
			}
			return false;
			
		} else {
			return ($result['conflicts'] == 1) ? false : true;
		}
		
	}
	
	public static function createGroup(user &$userObj, array $newGroup){
		$dbCon = db::singleton();
		//$newGroup = array_map(call_user_func('db::real_escape_string'), $newGroup);
		$sql = "INSERT INTO " . SYS_DB_DBNAME . ".group (institution_instID, name, description, private, byInvitation) VALUES (" . $dbCon->real_escape_string($newGroup['Institution']) . ", '" . $dbCon->real_escape_string($newGroup['Name']) . "', '" . $dbCon->real_escape_string($newGroup['Description']) . "', " . $dbCon->real_escape_string($newGroup['IsPrivate']) . ", " . $dbCon->real_escape_string($newGroup['InviteOnly']) . ")";
		$dbCon->single($sql);
		
		$groupID = $dbCon->insert_id;
		$sql = "INSERT INTO " . SYS_DB_DBNAME . ".group_user (user_uID, group_gID, isAdmin, active) VALUES (" . $dbCon->real_escape_string( $userObj->getuID() ) . ", " . $groupID . " , 1, 1)";
		$dbCon->single($sql);
		
		return array("status" => "success", "groupID" => $groupID);
	}
	
	public static function updateGroup(user &$userObj, $gID, array $details){
		// Does the user have permission to do this?
		if($userObj->isInGroup($gID, user::GROUP_ADMINONLY) && is_integer($gID)){
			$dbCon = db::singleton();
			$sql = "UPDATE " . SYS_DB_DBNAME . ".group SET name='" . $dbCon->real_escape_string($details['name']) . "', description='" . $dbCon->real_escape_string($details['description']) . "' WHERE gID=" . $dbCon->real_escape_string($gID);
			$dbCon->single($sql);
			return true;
		} else {
			throw new PicBoardException("User does not have permission to edit this group, or groupID is not an integer.");
		}
	}
	
	
	public static function deleteGroup(user &$userObj, $gID){
		$dbCon = db::singleton();
		
		if($userObj->getAccessLevel() < 0) throw new Exception("Permission Denied to delete group");
			
		$dbCon->startTransaction();
		
		try {
			// Delete users
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".group_user WHERE group_gID=" . $dbCon->real_escape_string((int)$gID);
			$dbCon->single($sql);
			
			
			// Get any images that are stored.
			$sql = "SELECT imgID FROM " . SYS_DB_DBNAME . ".image LEFT JOIN image_imageset ON image.imgID = image_imageset.image_imgID LEFT JOIN imageset ON image_imageset.imageset_isID=imageset.isID WHERE imageset.group_gID=" . $dbCon->real_escape_string((int)$gID);
			$images = $dbCon->single($sql);
			
			foreach($images as $image) { 
				$dbCon->single("DELETE FROM " . SYS_DB_DBNAME . ".image WHERE imgID=" . $image['imgID']);
				$dbCon->single("DELETE FROM " . SYS_DB_DBNAME . ".image_imageset WHERE image_imgID=" . $image['imgID']);
			}
			
			
			// Delete imagesets
			$dbCon->single("DELETE FROM " . SYS_DB_DBNAME . ".imageset WHERE group_gID=" . $dbCon->real_escape_string((int)$gID));
			
			
			// Delete charts
			$charts = $dbCon->single("SELECT chID FROM " . SYS_DB_DBNAME . ".chart WHERE group_gID=" . $dbCon->real_escape_string((int)$gID));
			foreach($charts as $chart){
				chart::deleteChart($userObj, $chart['chID']);
				$dbCon->single("DELETE FROM " . SYS_DB_DBNAME . ".book_chart WHERE chart_chID=" . $dbCon->real_escape_string($chart['chID']));
			}
			
			// Delete books
			$dbCon->single("DELETE FROM " . SYS_DB_DBNAME . ".book WHERE group_gID=" . $dbCon->real_escape_string((int)$gID));
			
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".group WHERE gID=" . $dbCon->real_escape_string((int)$gID);
			
			$dbCon->commit();
			return true;
		} catch (Exception $e){
			$dbCon->rollback();
			return false;
		}
			
	}

}

?>